﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using AD.Model;
using System.Data.SqlClient;

namespace AD.DAL
{
    public class PresentChargeDAL : DALBase
    {
        public static List<PresentChargeMod> QueryByPage(string filter, int pageSize, int index, out int maxcount)
        {
            maxcount = 0;
            try
            {
                maxcount = Count(filter);
                int size = index * pageSize;
                if (maxcount < size)
                    size = maxcount;
                //string sql = "select top {0} * from tab_present_charge_info where (AutoID NOT IN (select top {1} AutoID from tab_present_charge_info order by AutoID)) order by AutoID";
                string sql = "select top {0} * from tab_present_charge_info where (AutoID NOT IN (select top {1} AutoID from tab_present_charge_info order by AutoID)) order by AutoID";
                sql = string.Format(sql, pageSize, size);
                if (!string.IsNullOrEmpty(filter))
                {
                    sql = "select top {0} * from tab_present_charge_info where {2} and (AutoID NOT IN (select top {1} AutoID from tab_present_charge_info where {2} order by AutoID)) order by AutoID";
                    sql = string.Format(sql, pageSize, size, filter);
                }
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<PresentChargeMod> accountFlows = new List<PresentChargeMod>();
                foreach (DataRow row in dt.Rows)
                {
                    PresentChargeMod user = SetModel(row);
                    accountFlows.Add(user);
                }
                return accountFlows;
            }
            catch { return null; }
        }
        public static int Count(string filter)
        {
            return QueryFilterToTable(filter).Rows.Count;
        }
        public static int Delete(string id)
        {
            try
            {
                string sql = "delete tab_present_charge_info where AutoID="+id;
                return ExecuteNonQuery(sql);
            }
            catch
            {
            }
            return 0;
        }
        public static int Update(PresentChargeMod presentCharge)
        {
            try
            {
                string sql = "update tab_present_charge_info set Title=@Title,OneTimeMoney=@OneTimeMoney,PresentBalancePoint=@PresentBalancePoint,Remarks=@Remarks where AutoID=@AutoID";
                SqlParameter[] sqlParams = GetSqlParameter(presentCharge);
                return ExecuteNonQuery(sql, sqlParams);
            }
            catch
            {
            }
            return 0;
        }
        public static int Insert(PresentChargeMod presentCharge)
        {
            try
            {
                string sql = "insert into tab_present_charge_info(Title,OneTimeMoney,PresentBalancePoint,Remarks) values(@Title,@OneTimeMoney,@PresentBalancePoint,@Remarks)";
                SqlParameter[] sqlParams = GetSqlParameter(presentCharge);
                return ExecuteNonQuery(sql, sqlParams);
            }
            catch
            {
            }
            return 0;
        }
        public static PresentChargeMod QueryFrist(string filter)
        {
            IList<PresentChargeMod> tmp = QueryFilterToList(filter);
            if (tmp != null) return tmp[0];
            return null;
        }

        public static List<PresentChargeMod> QueryFilterToList(string filter)
        {
            try
            {
                DataTable dt = QueryFilterToTable(filter);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<PresentChargeMod> users = new List<PresentChargeMod>();
                foreach (DataRow row in dt.Rows)
                {
                    PresentChargeMod user = SetModel(row);
                    users.Add(user);
                }
                return users;
            }
            catch
            {
            }
            return null;
        }
      

        public static DataTable QueryFilterToTable(string filter)
        {
            try
            {
                string sql = "Select * from tab_present_charge_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                //sql  += " order by AutoID desc";
                return ExecuteDataTable(sql);
            }
            catch
            {
            }
            return null;
        }

        #region
        static PresentChargeMod SetModel(DataRow row)
        {
            PresentChargeMod user = new PresentChargeMod();
            user.AutoID = int.Parse(row["AutoID"].ToString());

            if (row["OneTimeMoney"] != DBNull.Value)
                user.OneTimeMoney = double.Parse(row["OneTimeMoney"].ToString());

            if (row["PresentBalancePoint"] != DBNull.Value)
                user.PresentBalancePoint = int.Parse(row["PresentBalancePoint"].ToString());

            if (row["Remarks"] != DBNull.Value)
                user.Remarks = row["Remarks"].ToString();

            if (row["Title"] != DBNull.Value)
                user.Title = row["Title"].ToString();
            return user;
        }
        static SqlParameter[] GetSqlParameter(PresentChargeMod presentCharge)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            SqlParameter AutoID = new SqlParameter("@AutoID", SqlDbType.Int, 4, "AutoID");
            AutoID.Value = presentCharge.AutoID;
            param.Add(AutoID);

            SqlParameter OneTimeMoney = new SqlParameter("@OneTimeMoney", SqlDbType.Money, 4, "OneTimeMoney");
            OneTimeMoney.Value = presentCharge.OneTimeMoney;
            param.Add(OneTimeMoney);

            SqlParameter PresentBalancePoint = new SqlParameter("@PresentBalancePoint", SqlDbType.Int, 4, "PresentBalancePoint");
            PresentBalancePoint.Value = presentCharge.PresentBalancePoint;
            param.Add(PresentBalancePoint);

            SqlParameter Remarks = new SqlParameter("@Remarks", SqlDbType.VarChar, 1024, "Remarks");
            Remarks.Value = presentCharge.Remarks;
            param.Add(Remarks);

            SqlParameter Title = new SqlParameter("@Title", SqlDbType.VarChar, 256, "Title");
            Title.Value = presentCharge.Title;
            param.Add(Title);

            return param.ToArray();
        }
        #endregion
    }
}